package com.sl.au.repository;

import com.sl.au.entity.*;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Transactional(readOnly = true)

public interface AfterSaleActiveRepository extends JpaRepository<AfterSaleActive, String> {

	@Query(" select a from AfterSaleActive a where  a.projectName like '%'+?1+'%' or a.orderUnit like '%'+?1+'%' ")
	Page<AfterSaleActive> getName(String key, Pageable pageable);

	Page<AfterSaleActive> findAll(Pageable pageable);

	/**
	 * 得到所有可以新建售后设备信息的项目合同的供方合同号
	 * 
	 * @return
	 */
	@Query("select a.supplierCode from Contract a where a.supplierCode is not null "
			+ "and a.supplierCode != '' and isAfterSaleActive is null" + " order by a.createTime desc ")
	List<Object> getContractSupplierCodeList1();

	/**
	 * 根据查询条件查询项目合同数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' ) and c.isAfterSale = true and c.id in (select mid.contractID from MaintainItemDevice mid group by mid.contractID ) ")
	Page<Contract> getPage(String key, Pageable pageable);

	/**
	 * 根据查询条件查询项目合同数据条数
	 * 
	 * @param key
	 * @return
	 */
	@Query(" select count(c) from Contract c where ( c.supplierCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' ) and c.isAfterSale = true and c.id in (select mid.contractID from MaintainItemDevice mid group by mid.contractID ) ")
	long getPageCount(String key);

	/**
	 * 得到当前合同的维护项目(即合同的维护计划)
	 * 
	 * @param contractID
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.contractID=?1 order by DATEADD(day,mid.maintainPeriod,mid.startDate) asc ")
	Page<MaintainItemDevice> getPlanPage(String contractID, Pageable pageable);

	/**
	 * 得到当前合同的维护项目条数
	 * 
	 * @param contractID
	 * @return
	 */
	@Query(" select count(mid) from MaintainItemDevice mid where mid.contractID=?1 ")
	Long getPlanPageCount(String contractID);

	/**
	 * 得到当前合同的维护项目
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.contractID=?1 ")
	Page<MaintainItemDevice> findMaintainPlans(String key, Pageable pageable);

	/**
	 * 得到当前合同的维护项目(即合同的维护计划)
	 * 
	 * @param contractID
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.contractID=?1 order by DATEADD(day,mid.maintainPeriod,mid.startDate) asc ")
	List<MaintainItemDevice> getMaintainPlansByContractID(String contractID);

}
